package com.dudu.dao;

import java.sql.Connection;
import java.util.List;

import net.sf.json.JSONObject;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;

import com.dudu.model.PoolAccountRel;

import core.CoreDao;
import core.db.DBConnectionUtil;
/**
 * 账号与号码能力配置dao
 * @author liyalei
 * @version 2016年3月7日 下午4:17:08
 *
 */
public class PoolAccountRelDao {
private static final Logger log =  core.util.Logger.getLogger(PoolAccountRelDao.class.getSimpleName());
	
	
	/**
	 * 新建账号与号码能力关系信息
	 * @param dao
	 * @param poolAccountRel 
	 * @return 成功返回0，失败返回-1
	 */
	public int insert(CoreDao dao, PoolAccountRel poolAccountRel) {
		log.info("PoolAccountRelDao.insert");
		//数据库连接
		Connection con = null;
		int result = -1;
		try {
			//获取数据库连接
			con = DBConnectionUtil.getDBConnection("emate1");
			//执行sql拼写
			//pool_num_id app_key cust_account periphery_code number_vest
			//email_notice switch_num create_time update_time
			StringBuffer sql = new StringBuffer(
					"INSERT INTO t_pool_account_rel(pool_num_id, app_key, cust_account,periphery_code ,"
					+ "number_vest,email_notice, switch_num, "
					+ "create_time, update_time)");
			sql.append(" VALUES(?,?,?,?,?,?,?,sysdate(),sysdate())");
			log.info("insert.sql="+sql);
			//执行sql
			List<JSONObject> list = dao.serviceJsonSql(
					con,
					sql.toString(),
					new Object[] { 
						poolAccountRel.getPool_num_id(),
						poolAccountRel.getApp_key(),
						poolAccountRel.getCust_account(),
						poolAccountRel.getPeriphery_code(),
						poolAccountRel.getNumber_vest(),
						poolAccountRel.getEmail_notice(),
						poolAccountRel.getSwitch_num()
			});

			JSONObject obj = list.get(0);
			result = obj.optInt("result");

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}
	
	/**
	 * 编辑账号与号码能力关系信息
	 * @param dao
	 * @param poolAccountRel
	 * @return 成功返回0，失败返回-1
	 */
	public int update(CoreDao dao, PoolAccountRel poolAccountRel) {
		log.info("PoolAccountRelDao.update");
		//数据库连接
		Connection con = null;
		int result = -1;

		try {
			//获取数据库连接
			con = DBConnectionUtil.getDBConnection("emate1");
			//拼写SQL
			//pool_num_id app_key cust_account periphery_code number_vest
			//email_notice switch_num create_time update_time
			StringBuffer sql = new StringBuffer(
					" UPDATE t_pool_account_rel set pool_num_id=?,app_key=?,cust_account=?,periphery_code=?,number_vest=?"
					+ ",update_time=?");
			sql.append(" where id=?");
			log.info("update.sql="+sql);
			//执行sql，更新相应数据
			List<JSONObject> list = dao.serviceJsonSql(
					con,
					sql.toString(),
					new Object[] {
						poolAccountRel.getPool_num_id(),
						poolAccountRel.getApp_key(),
						poolAccountRel.getCust_account(),
						poolAccountRel.getPeriphery_code(),
						poolAccountRel.getNumber_vest(),
						poolAccountRel.getUpdate_time(),
						poolAccountRel.getId()
			});

			if (list != null && list.size() > 0) {
				JSONObject obj = list.get(0);
				result = obj.optInt("result");
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}
	
	/**
	 * 根据账号id、号码能力表id删除关系表配置信息
	 * @param dao
	 * @param app_key 应用标识
	 * @param cust_account 账号标识
	 * @param pool_num_id 号码能力表id
	 * @return
	 */
	public int deleteByAccAbiId(CoreDao dao, String app_key, String cust_account,String pool_num_id) {
		log.info("PoolAccountRelDao.deleteByAccAbiId");
		//数据库连接
		Connection con = null;
		int result = -1;
		try {
			//获取数据库连接
			con = DBConnectionUtil.getDBConnection("emate1");
			//拼写sql
			StringBuffer sql = new StringBuffer("DELETE FROM t_pool_account_rel where app_key=? and cust_account=? AND pool_num_id=?");
			log.info("deleteByAccAbiId.sql="+sql);
			//执行sql
			List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(),
					new Object[] { app_key, cust_account,pool_num_id });

			if (list != null && list.size() > 0) {
				JSONObject obj = list.get(0);
				result = obj.optInt("result");
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}
	
	
	/**
	 * 查询所有PoolNumber信息
	 * @param dao
	 * @param pageSql
	 * @param paramSql
	 * @return
	 */
	public List<PoolAccountRel> queryAllPoolAccRel(CoreDao dao,String pageSql, String paramSql) {
		log.info("PoolAccountRelDao.queryAllPoolAccRel");
		//数据库连接
		Connection con = null;
		//最后需返回的
		List<PoolAccountRel> result = null;
		
		try {
			//获取数据库连接
			con = DBConnectionUtil.getDBConnection("emate1");
			//拼写sql
			StringBuffer sql = new StringBuffer("SELECT * FROM t_pool_account_rel");
			
			//判断如果查询条件不为空，则拼写sql
			if(StringUtils.isNotEmpty(paramSql)){
				sql.append(" WHERE ");
				sql.append(paramSql);
			}
			sql.append(" ORDER BY create_time DESC ");
			sql.append(pageSql);
			log.info("queryAllPoolAccRel="+sql);
			//查询数据
			List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(), new Object[] { null });
			if (list != null) {
				List<PoolAccountRel> items = dao.covertToBean(list, PoolAccountRel.class);
				result = items;
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
		
	}
	
	/**
	 * 获取号码池信息的条数
	 * @param dao
	 * @param paramSql
	 * @return
	 */
	public int getTotalPoolAccRel(CoreDao dao, String paramSql) {
		log.info("PoolAccountRelDao.getTotalPoolAccRel");
		if(StringUtils.isNotBlank(paramSql)){
			paramSql = " and " + paramSql;
		}else{
			paramSql="";
		}
		Connection con = null;
		int result = -1;
		try {
			con = DBConnectionUtil.getDBConnection("emate1");
			StringBuffer sql = new StringBuffer("select count(1) as totalPoolAccRel from t_pool_account_rel tpn where 1=1");
			sql.append(paramSql);

			List<JSONObject> list = dao.serviceJsonSql(
					con,
					sql.toString(),
					new Object[] { });

			JSONObject obj = list.get(0);
			result = (Integer) obj.get("totalPoolAccRel");
			log.info("getTotalPoolAccRel="+sql.toString());

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}
	
	
	/**
	 * 根据账号id查询所有账号与号码能力关系信息，并按照能力类型分别统计个数
	 * @param dao
	 * @param cust_account 账号标识
	 * @param app_key      应用标识
	 * @return
	 */
	public List<PoolAccountRel> getTotalByAccoutId(CoreDao dao, String cust_account,String app_key) {
		log.info("PoolAccountRelDao.queryAllPoolAccRel");
		//数据库连接
		Connection con = null;
		//最后需返回的
		List<PoolAccountRel> result = null;
		
		try {
			//获取数据库连接
			con = DBConnectionUtil.getDBConnection("emate1");
			//拼写sql
			StringBuffer sql = new StringBuffer("SELECT COUNT(tpr.id) AS id,tpa.ability_type FROM t_pool_account_rel tpr,t_pool_ability_info tpa");
			sql.append(" WHERE tpr.pool_num_id=tpa.id AND tpr.cust_account=? and tpr.app_key=?");
			sql.append(" GROUP BY tpa.ability_type;");

			log.info("queryAllPoolAccRel="+sql);
			//查询数据
			List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(), new Object[] { cust_account,app_key });
			if (list != null) {
				List<PoolAccountRel> items = dao.covertToBean(list, PoolAccountRel.class);
				result = items;
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		
		return result;
	}
	
	
	/**
	 * 查询rel表中当前账户的rel关系中第一条数据的email_notice，switch_num值
	 *
	 * @param dao
	 * @param app_key 应用标识 
	 * @param cust_account 账号标识
	 * @return
	 */
	public JSONObject getEmailSwitchByAccoutId(CoreDao dao, String app_key,String cust_account) {
		log.info("PoolAccountRelDao.getEmailNoticeByAccoutId");
		//数据库连接
		Connection con = null;
		//最后需返回的
		JSONObject json = new JSONObject();
		String emailNotice = "no";
		String switchNum = "no";
		
		try {
			//获取数据库连接
			con = DBConnectionUtil.getDBConnection("emate1");
			//拼写sql
			StringBuffer sql = new StringBuffer("SELECT tpr.email_notice,tpr.switch_num FROM t_pool_account_rel tpr WHERE tpr.cust_account=? and tpr.app_key=?");
			
			//查询数据
			List<JSONObject> list = dao.serviceJsonSql(con, sql.toString(), new Object[] { cust_account,app_key });
			if (list != null && list.size() > 0) {
				List<PoolAccountRel> items = dao.covertToBean(list, PoolAccountRel.class);
				emailNotice = items.get(0).getEmail_notice();
				switchNum = items.get(0).getSwitch_num();
				
				// 若为空或为no，则设置为no
				if(!emailNotice.equals("yes")){
					emailNotice = "no";
				}
				// 若为空或为no，则设置为no
				if(!switchNum.equals("yes")){
					switchNum = "no";
				}
			}else{
				emailNotice = "none";
				switchNum = "none";
			}

			json.put("emailNotice", emailNotice);
			json.put("switchNum", switchNum);
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		
		return json;
	}
	
	
	/**
	 * 根据账号id和邮件通知，更新该账号下所有rel关系中邮件通知内容
	 * @param dao
	 * @param cust_account 账号标识
	 * @param app_key 应用标识
	 * @param emailNotice 邮件通知
	 * @return
	 */
	public int updateEmailNoticeByAccId(CoreDao dao, String app_key,String cust_account, String emailNotice) {
		log.info("PoolAccountRelDao.updateEmailNoticeByAccId");
		Connection con = null;
		int result = -1;
		try {
			con = DBConnectionUtil.getDBConnection("emate1");
			StringBuffer sql = new StringBuffer("UPDATE t_pool_account_rel SET email_notice=? WHERE app_key=? and cust_account=?");

			List<JSONObject> list = dao.serviceJsonSql(
					con,
					sql.toString(),
					new Object[] {emailNotice, app_key,cust_account}
			);
			if (list != null && list.size() > 0) {
				JSONObject obj = list.get(0);
				result = obj.optInt("result");
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}
	
	/**
	 * 根据账号id和主动切换号码，更新该账号下所有rel关系中主动切换号码内容
	 * @param dao
	 * @param cust_account 账号标识
	 * @param app_key 应用标识
	 * @param switchNum 主动切换号码
	 * @return
	 */
	public int updateSwitchNumByAccId(CoreDao dao, String cust_account,String app_key, String switchNum) {
		log.info("PoolAccountRelDao.updateSwitchNumByAccId");
		Connection con = null;
		int result = -1;
		try {
			con = DBConnectionUtil.getDBConnection("emate1");
			StringBuffer sql = new StringBuffer("UPDATE t_pool_account_rel SET switch_num=? WHERE cust_account=? and app_key=?");

			List<JSONObject> list = dao.serviceJsonSql(
					con,
					sql.toString(),
					new Object[] {switchNum, cust_account,app_key}
			);
			if (list != null && list.size() > 0) {
				JSONObject obj = list.get(0);
				result = obj.optInt("result");
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			DBConnectionUtil.close(null, null, con);
		}
		return result;
	}
	
}
